/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dbtest;

import java.sql.*;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Anders
 */
public class DbConnector {

    String URL = "jdbc:mysql://localhost:3306/chess?user=root&password=0007";
    Connection c = DriverManager.getConnection(URL);
    Statement st = c.createStatement();
    ResultSet rs;

    public DbConnector() throws InstantiationException, IllegalAccessException, SQLException, ClassNotFoundException {

        Class.forName("com.mysql.jdbc.Driver").newInstance();
        //c.setAutoCommit(false);

    }

    public void createPlayer(String name) throws SQLException, ClassNotFoundException {
        st.executeUpdate("INSERT INTO players (name) VALUES ('" + name + "')");

    }
    
    public int getTurnNumber(int gameId) {
        int n;
        try {
            rs = st.executeQuery("SELECT turn_number FROM gamestates WHERE gameid = " + gameId);
        } catch (SQLException ex) {
            Logger.getLogger(DbConnector.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            rs.next();
            n = rs.getInt("turn_number");
        } catch (SQLException ex) {
            Logger.getLogger(DbConnector.class.getName()).log(Level.SEVERE, null, ex);
            n = 0;
        }
        return n;
    }

    public void createGame(int player1id, int player2id) throws InstantiationException, IllegalAccessException, SQLException, ClassNotFoundException {

        st.executeUpdate("INSERT INTO games (player1id, player2id) VALUES ('" + player1id + "','" + player2id + "')");
    }

    public void makeMove(int gameid, String piece, String pos) throws InstantiationException, IllegalAccessException, SQLException, ClassNotFoundException {
        //uppdaterar tabellen gamestates med pjäsens koordinater
       // int turnNumber = getTurnNumber(gameid);
        
        
        st.executeUpdate("UPDATE gamestates SET " + piece + " = '" + pos + "', turn_number = turn_number + 1 WHERE gameid = '" + gameid + "'");
        
    }

    public void initBoard(int gameid) throws SQLException, ClassNotFoundException {
        st.executeUpdate("INSERT INTO gamestates (gameid,"
                + " b_king, b_queen, b_bishop1, b_bishop2, b_knight1, b_knight2, b_rook1, b_rook2,"
                + " b_pawn1, b_pawn2, b_pawn3, b_pawn4, b_pawn5, b_pawn6, b_pawn7, b_pawn8,"
                + "w_king, w_queen, w_bishop1, w_bishop2, w_knight1, w_knight2, w_rook1, w_rook2,"
                + " w_pawn1, w_pawn2, w_pawn3, w_pawn4, w_pawn5, w_pawn6, w_pawn7, w_pawn8)"
                + "VALUES ('" + gameid + "',"
                + "'e8', 'd8', 'c8', 'f8', 'b8', 'g8', 'a8', 'h8',"
                + " 'a7', 'b7', 'c7', 'd7', 'e7', 'f7', 'g7', 'h7',"
                + " 'e1', 'd1', 'c1', 'f1', 'b1', 'g1', 'a1', 'h1',"
                + " 'a1', 'a2', 'a3', 'a4', 'a5', 'a6', 'a7', 'a8')");
    }

    public ArrayList<String> getBoard(int gameid) throws SQLException {
        ArrayList<String> board = new ArrayList<>();
        rs = st.executeQuery("SELECT * FROM gamestates WHERE gameid = " + gameid);

        board.add(rs.getString("b_king"));
        board.add(rs.getString("b_queen"));
        board.add(rs.getString("b_bishop1"));
        board.add(rs.getString("b_bishop2"));
        board.add(rs.getString("b_knight1"));
        board.add(rs.getString("b_knight2"));
        board.add(rs.getString("b_rook1"));
        board.add(rs.getString("b_rook2"));
        board.add(rs.getString("b_pawn1"));
        board.add(rs.getString("b_pawn2"));
        board.add(rs.getString("b_pawn3"));
        board.add(rs.getString("b_pawn4"));
        board.add(rs.getString("b_pawn5"));
        board.add(rs.getString("b_pawn6"));
        board.add(rs.getString("b_pawn7"));
        board.add(rs.getString("b_pawn8"));
        board.add(rs.getString("w_king"));
        board.add(rs.getString("w_queen"));
        board.add(rs.getString("w_bishop1"));
        board.add(rs.getString("w_bishop2"));
        board.add(rs.getString("w_knight1"));
        board.add(rs.getString("w_knight2"));
        board.add(rs.getString("w_rook1"));
        board.add(rs.getString("w_rook2"));
        board.add(rs.getString("w_pawn1"));
        board.add(rs.getString("w_pawn2"));
        board.add(rs.getString("w_pawn3"));
        board.add(rs.getString("w_pawn4"));
        board.add(rs.getString("w_pawn5"));
        board.add(rs.getString("w_pawn6"));
        board.add(rs.getString("w_pawn7"));
        board.add(rs.getString("w_pawn8"));

        return board;
    }

    public boolean gameExist(int gameid) throws InstantiationException, IllegalAccessException, SQLException, ClassNotFoundException {

        rs = st.executeQuery("SELECT gameid FROM gamestates");
        while (rs.next()) {
            int currentGame = rs.getInt("gameid");
            if (currentGame == gameid) {
                return true;
            }
        }
        return false;
    }

    public ArrayList<String> getPlayers() throws InstantiationException, IllegalAccessException, SQLException, ClassNotFoundException {
        rs = st.executeQuery("SELECT * FROM players");
        ArrayList<String> names = new ArrayList<>();
        while (rs.next()) {
            String name = rs.getString("name");
            System.out.println("name: " + name);
            names.add(name);

        }
        return names;
    }

    public ArrayList<Integer> getHighscores() throws SQLException {
        ArrayList<Integer> highscore = new ArrayList();
        rs = st.executeQuery("SELECT * FROM players ORDER BY score DESC");
        while (rs.next()){
            int score = rs.getInt("score");
            highscore.add(score);
        }
        return highscore;
    }
        public ArrayList<String> getHighscoreNames() throws SQLException {
        ArrayList<String> names = new ArrayList();
        rs = st.executeQuery("SELECT * FROM players ORDER BY score DESC");
        while (rs.next()){
            String name = rs.getString("name");
            names.add(name);
        }
        return names;
    }

    public void closeConnection() throws SQLException {
        c.close();

    }
}
